"""
Case Type   : 数据库兼容--兼容b库语法
Case Name   : 兼容a库无dolphin插件，使用distinctrow查询，合理报错
Create At   : 2022.10.14
Owner       : z@wan005
Description :
    1.创建a库
    2.创建表
    3.插入数据
    4.使用distinctrow,对一列去重
    5.清理环境
Expect      :
    1.创建成功
    2.成功
    3.成功
    4.报错
    5.清理环境完成
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class Compatibility(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.tb_name = 'tb_grammar_case0050'
        self.db_name = 'db_grammar_case0050'

    def test_grammar(self):
        text = '---step1:创建a库;expect:创建成功---'
        self.log.info(text)
        sql_cmd1 = f"set dolphin.b_compatibility_mode to off;" \
                   f"drop database if exists {self.db_name};" \
                   f"create database {self.db_name} dbcompatibility ='a';"
        sql_res = self.pri_sh.execut_db_sql(sql_cmd1)
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_res,
                      '执行失败:' + text)

        text = '---step2:创建表;expect:成功---'
        self.log.info(text)
        sql_cmd2 = f"drop table if exists {self.tb_name};" \
                   f"create table {self.tb_name}(id int,name varchar(20)," \
                   f"age int,stuno int);"
        sql_res = self.pri_sh.execut_db_sql(sql_cmd2, dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_res,
                      '执行失败:' + text)

        text = '---step3:插入数据;expect:成功---'
        self.log.info(text)
        sql_cmd2 = f"insert into {self.tb_name} values(1,'zhangsan',18,23);" \
                   f"insert into  {self.tb_name} values(2,'zhangsan',19,24);"
        sql_res = self.pri_sh.execut_db_sql(sql_cmd2, dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertEquals(2, sql_res.count(self.constant.INSERT_SUCCESS_MSG),
                          '执行失败:' + text)

        text = '---step4:使用distinctrow,对一列去重;expect:报错---'
        self.log.info(text)
        sql_cmd2 = f"select distinctrow name from {self.tb_name};"
        sql_res = self.pri_sh.execut_db_sql(sql_cmd2, dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertIn('ERROR:  column "distinctrow" does not exist', sql_res,
                      '执行失败:' + text)

    def tearDown(self):
        text = '---step5:清理环境;expect:清理环境完成---'
        self.log.info(text)
        drop_cmd1 = f"drop table {self.tb_name};"
        drop_res = self.pri_sh.execut_db_sql(drop_cmd1,
                                             dbname=f'{self.db_name}')
        self.log.info(drop_res)
        drop_msg = self.pri_sh.execut_db_sql(f"reset dolphin.b_compatibility_mode; drop database if exists "
                                             f"{self.db_name};")
        self.log.info(drop_msg)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, drop_res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_msg,
                      '执行失败:' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
